package com.springapp.utils;

import com.springapp.sqlite.ConfigModel;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/4/12.
 */
public class SqliteUtils2 {
    public static String daName = "githubinfo";
    public static String tbName = "githubtable";

    //固定死的
    public static String gitUrl = "http://127.0.0.1:7005/rancher-catalog.git";  //https://github.com/492162921/java2GitHubAPI.git
    public static String projectNames = "rancher-catalog";  //
    public static String basePaths = "D:\\ESMMS";

    public static void main( String args[] )
    {
        //SqliteUtils.createDb();
        //SqliteUtils.createTable();
        //SqliteUtils.insert();
        SqliteUtils2.select();
    }

    public static void createDb()
    {

        Connection c = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:"+daName+".db");
        } catch ( Exception e ) {
            e.printStackTrace();
        }
        System.out.println("Opened database successfully");
    }


    public static void createTable( )
    {
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:"+daName+".db");
            System.out.println("Opened database successfully");

            stmt = c.createStatement();
            String sql = "CREATE TABLE  "+tbName +
                    "(ID INT PRIMARY KEY     NOT NULL," +
                    " githubDowloadPath           CHAR(200)   , " +
                    " githubUser             CHAR(200)    , " +
                    " githubPassword        CHAR(200), " +
                    " projectName        CHAR(200), " +
                    " basePath         CHAR(200))";
            stmt.executeUpdate(sql);
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            e.printStackTrace();
        }
        System.out.println("Table created successfully");
    }

    public static void insert()
    {
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:"+daName+".db");
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            stmt = c.createStatement();
            String sql = "INSERT INTO "+tbName+" (id,githubDowloadPath,githubUser,githubPassword,basePath,projectName) " +
                    "VALUES (3, gitUrl, '', '', 'D:\\ESMMS' ,'rancher-catalog');";
            stmt.executeUpdate(sql);



            stmt.close();
            c.commit();
            c.close();
        } catch ( Exception e ) {
            e.printStackTrace();
        }
        System.out.println("Records created successfully");
    }

    public static List<ConfigModel> select()
    {
        List<ConfigModel> list = new ArrayList<ConfigModel>();
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:"+daName+".db");
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery( "SELECT * FROM "+tbName+";" );
            while ( rs.next() ) {
                int id = rs.getInt("id");
                String githubDowloadPath = gitUrl; //rs.getString("githubDowloadPath");
                String githubUser = rs.getString("githubUser");
                String githubPassword = rs.getString("githubPassword");
                String projectName = projectNames; //rs.getString("projectName");
                String basePath = basePaths ; //rs.getString("basePath");
                basePath =  System.getProperty("user.dir")+ File.separator+".."+File.separator+"webapps"+File.separator+projectName;

                ConfigModel cm = new ConfigModel(githubDowloadPath,githubUser,githubPassword,basePath,projectName);
                list.add(cm);
            }
            rs.close();
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            e.printStackTrace();
        }
        System.out.println("Operation done successfully");
        return list;
    }


}
